package dql.com.datamanager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySqlDataManager {
	private Connection connection;
	private String password;
	private String username;
	
	public MySqlDataManager(String user, String pass){
		this.username=user;
		this.password=pass;
	}
	
	public void getConnection() throws ClassNotFoundException{
		
        String url="jdbc:mysql://localhost:3306/ordersystem";
        String driver="com.mysql.jdbc.Driver";
        
        try {
            Class.forName(driver);
            this.connection= DriverManager.getConnection(url,this.username,this.password);
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            
        }
     
    }
	
	public boolean addOrder(String table, String item, String code) throws ClassNotFoundException{
		this.getConnection();
        try {

            String InsertSQL = "insert orders(tablenumber,item,cost) values(?,?,?)";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setInt(1, Integer.parseInt(table));
            ps.setString(2, item);
            ps.setDouble(3, this.getPrice(item)*Double.parseDouble(code));
            ps.execute();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
		
		
	}
	
	public double getPrice(String name) throws ClassNotFoundException{
		this.getConnection();
        try {
            double price;
            String InsertSQL = "select price from pricetable where name = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setString(1, name);
            ResultSet rs =ps.executeQuery();
            rs.next();
            price=rs.getDouble("price");
            return price;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return 0;

		
	}
	
	public String getCode(String code) throws ClassNotFoundException{
		this.getConnection();
        try {
            String price="1";
            String InsertSQL = "select rate from codetable where code = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setString(1, code);
            ResultSet rs =ps.executeQuery();
            
            if(rs.next());
               price=rs.getString("rate");
            return price;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "1";

		
	}
	
	public String getType(String name) throws ClassNotFoundException{
		this.getConnection();
        try {
            String type;
            String InsertSQL = "select t from pricetable where name = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setString(1, name);
            ResultSet rs =ps.executeQuery();
            rs.next();
            type=rs.getString("t");
            this.connection.close();
            return type;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;

		
	}
	
	
	public boolean payOrder(String table) throws ClassNotFoundException{
		this.getConnection();
        try {
       
            String InsertSQL = "update orders set payed = ? where tablenumber=? and payed = 0";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setInt(1, 1);
            ps.setInt(2, Integer.parseInt(table));
            ps.execute();
      
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
		
		
	}
	 
	public String getOrder(String table) throws ClassNotFoundException{
		this.getConnection();
		String orders="";
		
        try {

            String InsertSQL = "select * from orders where tablenumber =? and payed = ?";
            PreparedStatement ps = this.connection.prepareStatement(InsertSQL);
            ps.setInt(1, Integer.parseInt(table));
            ps.setInt(2, 0);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
            	orders=orders+";"+rs.getString("item");
            	
            }
           
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return orders;
		
		
	}
	
	

}
